sewebfandomcom_ru-20200213-history
RallRate
Отчет RallRate - подсчет перехода кредитов в следующие группы просрочки: use AVD_LOAN_INF --select Count13 from D_2012_11_23_ where AgencyContractNum = '' UPDATE AVD_LOAN_INF.dbo.D_2012_11_23_ SET DaysProsr = 0 WHERE DaysProsr is null UPDATE AVD_LOAN_INF.dbo.D_2012_12_21_ SET DaysProsr = 0 WHERE DaysProsr is null SELECT max(AgencyName)as AgencyName,max(AgencyContractNum) as AgencyContractNum, max(ReestrNum) as ReestrNum,Count13, max(NPL) as NPL, max(DaysProsr)as DaysProsr into #t5 FROM AVD_LOAN_INF.dbo.D_2012_11_23_ where (AgencyContractNum <> '11-06/07_Íîâ') and (AgencyContractNum <> '6/07_ÑÏá') and (AgencyContractNum <> 'ÀÂÄ1/07_Êàëèíèíãðàä') group by Count13 SELECT max(AgencyName)as AgencyName,max(AgencyContractNum) as AgencyContractNum, max(ReestrNum) as ReestrNum, Count13, max(NPL) as NPL, max(DaysProsr) as DaysProsr into #t6 FROM AVD_LOAN_INF.dbo.D_2012_12_21_ where (AgencyContractNum <> '11-06/07_Íîâ') and (AgencyContractNum <> '6/07_ÑÏá') and (AgencyContractNum <> 'ÀÂÄ1/07_Êàëèíèíãðàä') group by Count13 ---------Ñëèâàåì âî âðåìåííóþ òàáëèöó t2210 CREATE TABLE #t1(Count13 varchar(15) PRIMARY KEY, NPL decimal(15,2), DaysProsr int, AgencyName varchar(45),AgencyContractNum varchar(65),ReestrNum varchar(55),Gr varchar(10)) INSERT INTO #t1 SELECT Count13, NPL, isnull(DaysProsr,0), AgencyName,AgencyContractNum, ReestrNum, CASE WHEN DaysProsr = 0 THEN 'a0' WHEN DaysProsr >= 1 and DaysProsr<=30 THEN 'b1-30' WHEN DaysProsr >= 31 and DaysProsr<=60 THEN 'c31-60' WHEN DaysProsr >= 61 and DaysProsr<=90 THEN 'd61-90' WHEN DaysProsr >= 91 and DaysProsr<=120 THEN 'e91-120' WHEN DaysProsr >= 121 and DaysProsr<=150 THEN 'f121-150' WHEN DaysProsr >= 151 and DaysProsr<=180 THEN 'g151-180' WHEN DaysProsr >= 181 then 'h181+' END from #t5 --select * from #t1 ---------Ñëèâàåì âî âðåìåííóþ òàáëèöó t2011 CREATE TABLE #t2(Count13 varchar(15) PRIMARY KEY, NPL decimal(15,2), DaysProsr int, AgencyName varchar(45),AgencyContractNum varchar(65),ReestrNum varchar(55),Gr varchar(10)) INSERT INTO #t2 SELECT Count13, NPL, isnull(DaysProsr,0), AgencyName,AgencyContractNum,ReestrNum, CASE WHEN DaysProsr = 0 THEN 'a0' WHEN DaysProsr >= 1 and DaysProsr<=30 THEN 'b1-30' WHEN DaysProsr >= 31 and DaysProsr<=60 THEN 'c31-60' WHEN DaysProsr >= 61 and DaysProsr<=90 THEN 'd61-90' WHEN DaysProsr >= 91 and DaysProsr<=120 THEN 'e91-120' WHEN DaysProsr >= 121 and DaysProsr<=150 THEN 'f121-150' WHEN DaysProsr >= 151 and DaysProsr<=180 THEN 'g151-180' WHEN DaysProsr >= 181 then 'h181+' END from #t6 --select * from #t2 SELECT #t1.AgencyName,#t1.AgencyContractNum , #t1.Gr, sum(#t1.NPL) as SNPL, count(#t1.Count13) as Quant into #t3 FROM #t1 INNER JOIN #t2 ON #t1.Count13 = #t2.Count13 where #t1.Gr <> #t2.Gr and #t1.DaysProsr <= #t2.DaysProsr group by #t1.Gr, #t1.AgencyName,#t1.AgencyContractNum --âûøëè ñ 0 íà 1-30 --SELECT #t1.AgencyName,#t1.AgencyContractNum ,#t2.ReestrNum, #t1.Gr, #t1.Count13, #t1.NPL, #t2.NPL, #t1.DaysProsr, #t2.DaysProsr --FROM #t1 INNER JOIN #t2 --ON #t1.Count13 = #t2.Count13 --where #t1.Gr <> #t2.Gr and #t1.DaysProsr <= #t2.DaysProsr and #t1.DaysProsr = 0 --group by #t1.Gr, #t1.AgencyName,#t1.AgencyContractNum --âûøëè ñ 60_90 íà 90+ --SELECT #t1.AgencyName,#t1.AgencyContractNum ,#t2.ReestrNum, #t1.Gr, #t1.Count13, #t1.NPL, #t2.NPL, #t1.DaysProsr, #t2.DaysProsr --FROM #t1 INNER JOIN #t2 --ON #t1.Count13 = #t2.Count13 --where #t1.Gr <> #t2.Gr and #t1.DaysProsr <= #t2.DaysProsr and #t1.DaysProsr <= 90 and #t1.DaysProsr >= 60 SELECT #t1.AgencyName,#t1.AgencyContractNum , #t1.Gr, sum(#t1.NPL) as SNPL, count(#t1.Count13) as Quant into #t4 FROM #t1 group by #t1.Gr,#t1.AgencyName,#t1.AgencyContractNum --select * from #t3 --select * from #t4 --select * from #t4 where AgencyContractNum = 'ÀÂÄ 29/07' select #t3.AgencyName, #t3.AgencyContractNum, #t3.Gr, CASE WHEN #t3.Gr= 'a0' then 'b1-30' WHEN #t3.Gr= 'b1-30' then 'c31-60' WHEN #t3.Gr= 'c31-60' then 'd61-90' WHEN #t3.Gr= 'd61-90' then 'e91-120' WHEN #t3.Gr= 'e91-120' then 'f121-150' WHEN #t3.Gr= 'f121-150' then 'g151-180' WHEN #t3.Gr= 'g151-180' then 'h181+' END ,#t3.SNPL/#t4.SNPL, #t3.Quant as OutQuant, #t4.Quant as TotQuant, #t3.SNPL as OutNPL, #t4.SNPL as TotNPL, CASE WHEN #t3.Gr= 'a0' then 1 WHEN #t3.Gr= 'b1-30' then 2 WHEN #t3.Gr= 'c31-60' then 3 WHEN #t3.Gr= 'd61-90' then 4 WHEN #t3.Gr= 'e91-120' then 5 WHEN #t3.Gr= 'f121-150' then 6 WHEN #t3.Gr= 'g151-180' then 7 END from #t3 inner join #t4 on (#t3.Gr = #t4.Gr) and (#t3.AgencyContractNum = #t4.AgencyContractNum) and (#t3.AgencyName = #t4.AgencyName) ----Óäàëÿåì âðåìåííûå òàáëèöû drop table #t5 drop table #t6 drop table #t1 drop table #t2 drop table #t3 drop table #t4 ----------------------------